﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using Teradata.Client.Provider;
using System.Data;
using System.Configuration;

public class BulkLoad
{
    public BulkLoad() { }
    public DataTable IncementalLoadTableFromTeradata(string psConn, string psSQL, string psTable)
    {
        return LoadTableFromTeradata(psConn, psSQL, psTable, true);
    }

    public DataTable LoadTableFromTeradata(string psConn, string psSQL, string psTable)
    {
        return LoadTableFromTeradata(psConn, psSQL, psTable, false);
    }

    public DataTable LoadTableFromTeradata(string psConn, string psSQL, string psTable, bool pbIsIncrementalLoad)
    {
        string sSQLServerCn = ConfigurationManager.ConnectionStrings["ACC_NEW"].ConnectionString;
        SqlConnection cnSQLServer = null;
        // Open a connection to the Teradata database.
        using (TdConnection cn = new TdConnection(psConn))
        {
            cnSQLServer = new SqlConnection(sSQLServerCn);
            
            cnSQLServer.Open();
            SqlCommand commandRowCount = null;
            Caching.CachingText = psTable.Replace(".", pbIsIncrementalLoad ? "+" : "=") + " CLEARING";
            if (pbIsIncrementalLoad)
            {
                // Clear the CURRENT month in the destination table.
                if (psTable.Contains(".ORDERS"))
                    commandRowCount = new SqlCommand("DELETE " + psTable + " WHERE MONTH(START_DT)=MONTH(GETDATE()-1) AND YEAR(START_DT)=YEAR(GETDATE()-1)", cnSQLServer);
                else
                    commandRowCount = new SqlCommand("DELETE " + psTable + " WHERE MN=MONTH(GETDATE()-1) AND YR=YEAR(GETDATE()-1)", cnSQLServer);
            }
            else
                // Clear the destination table.
                commandRowCount = new SqlCommand("TRUNCATE TABLE " + psTable, cnSQLServer);
            commandRowCount.CommandTimeout = 0;
            commandRowCount.ExecuteScalar();
            cnSQLServer.Close();

            Caching.CachingText = psTable.Replace(".", pbIsIncrementalLoad ? "+" : "=") + " READING";

            // Get data from the source table as a DataReader.
            cn.Open();
            TdCommand commandSourceData = new TdCommand(psSQL, cn);
            commandSourceData.CommandTimeout = 0;
            TdDataReader reader = commandSourceData.ExecuteReader();


            // Set up the bulk copy object using a connection string. 
            // In the real world you would not use SqlBulkCopy to move
            // data from one table to the other in the same database.

            Caching.CachingText = psTable.Replace(".", pbIsIncrementalLoad ? "+" : "=") + " WRITING";
            using (
                SqlBulkCopy bulkCopy = new SqlBulkCopy(sSQLServerCn))
            {
                bulkCopy.DestinationTableName = psTable;

                bulkCopy.BulkCopyTimeout = 0;

                try
                {
                    // Write from the source to the destination.
                    bulkCopy.WriteToServer(reader);
                }

                finally
                {
                    // Close the SqlDataReader. The SqlBulkCopy
                    // object is automatically closed at the end
                    // of the Using block.
                    reader.Close();
                }
            }

             DataTable dtSQLServer = new DataTable();

            //using (cnSQLServer)
            //{
            //    cnSQLServer.Open();
            //    using (SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM " + psTable, cnSQLServer))
            //    {
            //        da.SelectCommand.CommandTimeout = 0;
            //        da.Fill(dtSQLServer);
            //    }
            //}

            return dtSQLServer;
        }
    }

}